package com.uncp.comisiondeadmision.comprobantedepago.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;



import com.uncp.comisiondeadmision.comprobantedepago.beans.ComprobanteBean;
import com.uncp.comisiondeadmision.comprobantedepago.beans.PostulanteBean;
import com.uncp.comisiondeadmision.comprobantedepago.beans.PostulanteDniMix;
import com.uncp.comisiondeadmision.util.MySqlDBConn;

public class MySqlComprobanteDAO implements ComprobanteDAO {
	@Override
	public PostulanteBean validaCDP(String cdp) throws SQLException {
		
		Connection conn = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		
		try {
			
			MySqlDBConn mysql = new MySqlDBConn();
			conn = mysql.getConnection();
			
			String sql = "select p.*,s.nrosolicitud " +
					"from cdp c inner join postulante p " +
					"on c.codpostulante=p.codpostulante " +
					"inner join solicitud s " +
					"on p.codpostulante=s.codpostulante " +
					"where c.codcdp=?";
			
			pstm = conn.prepareStatement(sql);
			pstm.setString(1, cdp);
			
			// 3 consume el Resulset
			rs = pstm.executeQuery();
			PostulanteBean bean = null;
			
			if(rs.next()) {
				bean = new PostulanteBean();
				bean.setIntCodPostulante(rs.getInt(1));
				bean.setStrNombres(rs.getString(2));
				bean.setStrApepat(rs.getString(3));
				bean.setStrApemat(rs.getString(4));
				bean.setStrFechaNac(rs.getString(5));
				bean.setStrDni(rs.getString(6));
				bean.setStrEmail(rs.getString(7));
				bean.setStrTelefono(rs.getString(8));
				bean.setBlobImagen(rs.getBytes(9));
				bean.setStrEstado(rs.getString(10));
				bean.setIntCodModalidad(rs.getInt(11));
				bean.setIntCodCarrera(rs.getInt(12));
				bean.setIntCodSolicitud(rs.getInt(13));
				return bean;
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			if (rs != null)	 rs.close();
			if (pstm != null) pstm.close();
			if (conn != null)conn.close();
		}

		return null;
		
	}
	@Override
	public int insertaComprobante(ComprobanteBean comprobanteBean)
			throws SQLException {
		// TODO Auto-generated method stub
		Connection conn = null;
		PreparedStatement pstm = null;
		PreparedStatement pstm2 = null;
		String sql = "insert into cdp(CODCDP, CONCEPTO, FECHA, TOTAL, ESTADO, CODIGV, CODPOSTULANTE) values(?,?,?,?,?,?,?)";
		
		String sql2="insert into solicitud values (null,null,null,?)";
		try {
			conn = new MySqlDBConn().getConnection();
			pstm = conn.prepareStatement(sql);
			
			pstm2 = conn.prepareStatement(sql2);
			
			
			pstm.setString(1,comprobanteBean.getStrCodCdp());
			pstm.setString(2,comprobanteBean.getStrConcepto());
			pstm.setString(3,comprobanteBean.getStrFecha());
			pstm.setDouble(4,comprobanteBean.getDblTotal());
			pstm.setString(5,comprobanteBean.getStrEstado());
			pstm.setInt(6,comprobanteBean.getIntCodIgv());
			pstm.setInt(7,comprobanteBean.getIntCodPostulante());
			
			//Cuando se inserta el comprobante, se registra una solicitud en NULL
			pstm2.setInt(1,comprobanteBean.getIntCodPostulante());
			int conformidadCDP = -1;
			int conformidadSoli = -1;
			int conformidad=1;
			
			
			
			conformidadCDP=pstm.executeUpdate();
			conformidadSoli=pstm2.executeUpdate();
			
			if (conformidadCDP==1 && conformidadSoli==1) {
				System.out.println("Se insertaron los 22222222222");
				return conformidad;
			}else {
				return -1;
			}
			
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			conn.close();
			pstm.close();
		}
		return -1;
		
		
	}

	@Override
	public ArrayList<ComprobanteBean> listarComprobantes() throws SQLException {
		Connection con = null;
		Statement s =null;
		ResultSet rs = null;	
	    ArrayList<ComprobanteBean> array = new ArrayList<ComprobanteBean>(); 
		
		try {
			con = new MySqlDBConn().getConnection();
			s = con.createStatement();
			String sql = "SELECT CODCDP, CONCEPTO, FECHA, TOTAL, ESTADO, CODIGV, CODPOSTULANTE FROM cdp order by codpostulante desc;";
			rs = s.executeQuery(sql);
			
			
			ComprobanteBean bean;
			
			while(rs.next()){
				
		    bean = new ComprobanteBean();
		    bean.setStrCodCdp(rs.getString(1));
			bean.setStrConcepto(rs.getString(2));
			bean.setStrFecha(rs.getString(3));
			bean.setDblTotal(rs.getDouble(4));
			bean.setStrEstado(rs.getString(5));
			bean.setIntCodIgv(rs.getInt(6));
			bean.setIntCodPostulante(rs.getInt(7));
			array.add(bean);	
								
			}
	
		} catch (Exception e) {
			System.out.println(e.getMessage());
		}
		finally{
			try {
				if(con!=null)con.close();
				if(s!=null)s.close();
				if(rs!=null)rs.close();
			} catch (Exception e2) {
				System.out.println(e2.getMessage());
			}
		}

		return array;
	}
	
	@Override
	public ArrayList<PostulanteDniMix> listarInformacion(String dni) throws SQLException {
		Connection con = null;
		Statement s =null;
		ResultSet rs = null;	
	    ArrayList<PostulanteDniMix> array = new ArrayList<PostulanteDniMix>(); 
		
		try {
			con = new MySqlDBConn().getConnection();
			s = con.createStatement();
			String sql = "SELECT c.codcdp,c.concepto,c.fecha, p.CODPOSTULANTE, p.NOMBRES, p.APEPAT, p.APEMAT, p.FECHANAC, p.DNI, p.EMAIL, p.TELEFONO,"+
			"p.CODCARRERA FROM cdp c inner join postulante p on c.codpostulante=p.codpostulante where p.dni="+dni;
			rs = s.executeQuery(sql);
			
			
			PostulanteDniMix bean;
			
			while(rs.next()){
				
		    bean = new PostulanteDniMix();
		    bean.setStrDniCodCdp(rs.getString(1));
		    bean.setStrDniConcepto(rs.getString(2));
			bean.setStrDniFecha(rs.getString(3));
			bean.setIntDniCodPostulante(rs.getInt(4));
			bean.setStrDniNombres(rs.getString(5));
			bean.setStrDniApepat(rs.getString(6));
			bean.setStrDniApemat(rs.getString(7));
			bean.setStrDniFechaNac(rs.getString(8));
			bean.setStrDniDni(rs.getString(9));
			bean.setStrDniEmail(rs.getString(10));
			bean.setStrDniTelefono(rs.getString(11));
			bean.setIntDniCodCarrera(rs.getInt(12));
			array.add(bean);	
								
			}
	
		} catch (Exception e) {
			System.out.println(e.getMessage());
		}
		finally{
			try {
				if(con!=null)con.close();
				if(s!=null)s.close();
				if(rs!=null)rs.close();
			} catch (Exception e2) {
				System.out.println(e2.getMessage());
			}
		}

		return array;
	}
	

}
